/*
 * SQLManager.java
 *
 * Created on November 5, 2007, 11:31 PM
 *
 * To change this template, choose Tools | Template Manager
 * and open the template in the editor.
 */
 
package reloj;

import java.sql.*;
import java.text.SimpleDateFormat;
import javax.swing.JOptionPane;
/**
 *
 * @authorMario Batres
 */
public class SQLManager {
    private static final String user = "root";
    private static final String url = "jdbc:mysql://localhost/asistencia";
    private static final String password = "";
    
    public SQLManager(){
    
    }
      
    
    protected Connection getConexion(){
        try{    
            try{
                Class.forName("com.mysql.jdbc.Driver").newInstance();
            }catch(Exception e){
                return null;
            } 
            Connection conexion = DriverManager.getConnection(url, user, password);            
            return conexion;
        } catch (SQLException ex) {           
            JOptionPane.showMessageDialog(null, "SQLException: " + ex.getMessage());	
        }     
            
        return null;
    }
    
    public boolean isAdministrador(String login, String passwd){        
         try{                
            Connection conexion = this.getConexion();
            
            if(conexion != null){
                Statement s = conexion.createStatement();
                String query = "select * from Administrador where login='" + login + "' and passwd='" + passwd+ "';";
                ResultSet rs = s.executeQuery(query);                        
                boolean res = rs.next();                
                conexion.close();      
                return res;            
            }
            
        } catch (SQLException ex) {            
            //JOptionPane.showMessageDialog(null, "SQLException: " + ex.getMessage());	
        }     
        return false;
    }
    
    public boolean getMaestro(int id, String passw) {
         try{                
            Connection conexion = this.getConexion();
            
            if(conexion != null){
                Statement s = conexion.createStatement();
                String query = "select id, nombre, apellido, contrasenia, administrador from maestro where id =" + id + " and contrasenia ='" + passw + "'";
                ResultSet rs = s.executeQuery(query);                        
                boolean res = rs.next();                
                conexion.close();      
                return res;            
            }
        } catch (SQLException ex) {            
            JOptionPane.showMessageDialog(null, "SQLException: " + ex.getMessage());	
        }     
        return false;
    }
    
    public String getNombre(int id) {
        String name = null;
        try {                
            Connection conexion = this.getConexion();
            if(conexion != null){
                Statement s = conexion.createStatement();
                String query = "select nombre from maestro where id =" + id;
                s.executeQuery (query);
                ResultSet rs = s.getResultSet();
                int count = 0;
                while (rs.next ( )) {
                    name = rs.getString(1);
                    ++count;
                }
                rs.close ( ); // close result set
                s.close ( ); // close statement
                return(name);
            }
        } catch (SQLException ex) {            
            JOptionPane.showMessageDialog(null, "SQLException: " + ex.getMessage());	
        }     
        return null;
    }
    
    public boolean yaentro(int id, String fecha) {
        boolean yaentroalwhile = false;
        try {                
            Connection conexion = this.getConexion();
            if(conexion != null){
                Statement s = conexion.createStatement();
                String query = "select id, fecha from control where fecha = '" + fecha + "' and horasalida = '00:00:00' and id =" + id + ";";
                s.executeQuery (query);
                ResultSet rs = s.getResultSet();
                int count = 0;
                while (rs.next ( )) {
                    yaentroalwhile=true;
                    ++count;
                }
                rs.close ( ); // close result set
                s.close ( ); // close statement
            }
        } catch (SQLException ex) {            
            JOptionPane.showMessageDialog(null, "SQLException: " + ex.getMessage());	
        }
        return(yaentroalwhile);
    }
    
     public void InsertarRegistroSalida(int id, String hora, String fecha){
        try{
            Connection conexion = this.getConexion();
            if(conexion != null){
                Statement s = conexion.createStatement();                
               //UPDATE control
              // SET horasalida="3:22:00"
               // WHERE horaentrada != "00:00:00" and horasalida = "00:00:00" and id=8 
                String query = "update control set horasalida='";
                query += hora + "' where horaentrada !='00:00:00' and horasalida='00:00:00' and id= ";
                query += id;
                System.out.println(query);        
                s.executeUpdate(query);
                s.close();
                conexion.close();                
            }
        }catch(SQLException ex){
            JOptionPane.showMessageDialog(null, "SQLException: " + ex.getMessage(), "Insertar Registro", JOptionPane.ERROR_MESSAGE);
        }
    }
    
    
    public void InsertarRegistro(int id, String nombre){
        try{
            Connection conexion = this.getConexion();
            if(conexion != null){
                Statement s = conexion.createStatement();                
                
                String query = "insert into prueba values (";
                query += id + ", ";
                query += "'" + nombre + "');";
                        
                s.executeUpdate(query);
                s.close();
                conexion.close();                
            }
        }catch(SQLException ex){
            JOptionPane.showMessageDialog(null, "SQLException: " + ex.getMessage(), "Insertar Registro", JOptionPane.ERROR_MESSAGE);
        }
    }
    
        public void InsertarRegistroMarcar(int id, String horae, String horas, String fecha){
        try{
            Connection conexion = this.getConexion();
            if(conexion != null){
                Statement s = conexion.createStatement();                
                
                String query = "insert into control values (";
                query += id + ", ";
                query += "'" + horae + "',";
                query += "'" + horas + "',";
                query += "'" + fecha + "');";
                                  
                s.executeUpdate(query);
                s.close();
                conexion.close();                
            }
        }catch(SQLException ex){
            JOptionPane.showMessageDialog(null, "SQLException: " + ex.getMessage(), "Insertar Registro", JOptionPane.ERROR_MESSAGE);
        }
    }
}
